/*
	THIS PROCEDURE LOADS THE INVENTORY AVAILABLE FOR THE DATE SPECIFIED IN CHIPTRAC AND CALCULATES ITS COST.
	EXEC sp_cost_reprort_by_product '0','0','0','0','04/02/2007','0','0'
*/
Alter PROC sp_cost_reprort_by_product

@PROCESS_TYPE	VARCHAR(50),
@PRODUCTID	VARCHAR(50),
@PROCESSSTEP_ID	VARCHAR(200),
@VENDOR_ID	VARCHAR(50),
@ASOFDATE	VARCHAR(50),
@REPORT_TYPE	VARCHAR(20),
@DEFAULT_PROMPT	VARCHAR(50)
 
AS
BEGIN
	CREATE TABLE #COST_TABLE 
		( [LOT ID] INT,[PROCESSSTEP ID] INT, [LOT CATEGORY] VARCHAR(20),[WAFER QTY] INT, [QTY IN] INT,
		  [LOT COST] FLOAT,[LOT CUMMULATIVE COST] FLOAT ,[PART NUMBER] VARCHAR(50),[BASE PRODUCT] VARCHAR(50),
		  [PROCESS] VARCHAR(50),[PROCESS TYPE ID] INT,[VENDOR NAME] VARCHAR(50) )

 	DECLARE @SQLSTRING NVARCHAR(4000)

	DECLARE @TEMP_VARCHAR VARCHAR(50)
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME

	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			NULL AS LOT_ID,LTH.PROCESSSTEP_ID,LOT_CATEGORY,/*SUM(WAFER_QTY_IN)*/ WAFER_QTY_IN,/*SUM(QTY_IN)*/ QTY_IN,
			/*SUM(LOT_COST)*/ LOT_COST, LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE_NAME,
			(CASE
				WHEN PS_TYPE = ''FAB'' THEN 1
				WHEN PS_TYPE = ''WAFER BANK'' THEN 2
				WHEN PS_TYPE = ''SORT'' THEN 3
				WHEN PS_TYPE = ''DIE BANK'' THEN 4
				WHEN PS_TYPE = ''ASSEMBLY'' THEN 5
				WHEN PS_TYPE = ''FT'' THEN 6
				WHEN PS_TYPE = ''ReTest''  THEN  7
				WHEN PS_TYPE = ''QC'' THEN  8
				WHEN PS_TYPE = ''Ball Scan'' THEN 9
				WHEN PS_TYPE = ''Baking'' THEN 10
				WHEN PS_TYPE = ''FG'' THEN 11
			   END 
			) AS PS_TYPE_ID,L_VENDOR_NAME
		FROM 
			LOT_TABLE LTH,PRODUCT P,PROCESS_STEP PS ,LOCATION L
		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND P.P_TYPE IS NULL
			AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @SQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.PROCESSSTEP_ID,LTH.LOT_CATEGORY,LTH.WAFER_QTY_IN , LTH.QTY_IN,
			LT.LOT_COST, LT.LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE_NAME,
			(CASE
				WHEN PS_TYPE = ''FAB'' THEN 1
				WHEN PS_TYPE = ''WAFER BANK'' THEN 2
				WHEN PS_TYPE = ''SORT'' THEN 3
				WHEN PS_TYPE = ''DIE BANK'' THEN 4
				WHEN PS_TYPE = ''ASSEMBLY'' THEN 5
				WHEN PS_TYPE = ''FT'' THEN 6
				WHEN PS_TYPE = ''ReTest''  THEN  7
				WHEN PS_TYPE = ''QC'' THEN  8
				WHEN PS_TYPE = ''Ball Scan'' THEN 9
				WHEN PS_TYPE = ''Baking'' THEN 10
				WHEN PS_TYPE = ''FG'' THEN 11
			   END 
			) AS PS_TYPE_ID,L_VENDOR_NAME 
		FROM 
			LOT_TABLE_HISTORY LTH, LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS ,LOCATION L
		WHERE
			LTH.LOT_ID = LT.LOT_ID
			AND LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND P.P_TYPE IS NULL
			AND ( LTH.LOT_CURRENT_STATUS = ''RUNNING'' OR LTH.LOT_CURRENT_STATUS = ''HOLD'' )
			AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END 		
	
	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''

		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND LTH.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_ID +'''))'
		END
	END
	ELSE
	BEGIN
		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_ID +'''))'
		END
	END

	/*IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID = '''+ @PRODUCTID+''''
	END

	IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LTH.PROCESSSTEP_ID = '''+ @PROCESSSTEP_ID+''''
	END*/
	
	IF @VENDOR_ID IS NOT NULL AND @VENDOR_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.VENDOR_ID = '''+ @VENDOR_ID+''''
	END
		
	--PRINT '@SQLSTRING = '+@SQLSTRING
	EXEC (@SQLSTRING)	

	SELECT  [PROCESSSTEP ID],[LOT CATEGORY],SUM([WAFER QTY]) [WAFER QTY],SUM([QTY IN]) [QTY IN],
		[LOT COST],SUM([LOT CUMMULATIVE COST]) [LOT CUMMULATIVE COST],[PART NUMBER],
		[BASE PRODUCT],[PROCESS],[PROCESS TYPE ID]--,[VENDOR NAME] 
	FROM #COST_TABLE
	GROUP BY [PROCESSSTEP ID],[LOT CATEGORY],[LOT COST], [PART NUMBER],[BASE PRODUCT],
		 [PROCESS],[PROCESS TYPE ID]--,[VENDOR NAME] 

END
